package com.tech.java4ParseSql.process;//来自zpc

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.tech.config.ApplicationConfig;
import com.tech.java4ParseSql.entity.*;
import com.tech.java4ParseSql.enums.GroupCalculationValueTypeEnum;
import com.tech.java4ParseSql.util.AesUtils;
import com.tech.java4ParseSql.util.DatesUtil;
import com.tech.java4ParseSql.util.GetDateUtils;
import org.apache.commons.lang3.StringUtils;

import java.lang.reflect.Constructor;
import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * 群组规则配置
 *
 * @author zpc
 */
@SuppressWarnings("unchecked")
public class CustomerGroupCalculationServiceImpl {

//    public static void main(String[] args) {
//
//        CustomerGroupCalculationServiceImpl customerGroupCalculationService = new CustomerGroupCalculationServiceImpl();
//
//        Map<Integer, Object> map = new HashMap<>();
//
//        GroupCalculation groupCalculation = customerGroupCalculationService.getGroupCalculation("{\"attributeCondition\":{\"firstLogicalVal\":0,\"firstAttributeCondition\":[{\"secondLogicalVal\":0,\"secondAttributeCondition\":[{\"active\":false,\"attribute\":\"first_store\",\"attributeSymbol\":\"belong\",\"attributeType\":\"customerAttribute\",\"attributeValue\":\"101\",\"dataSource\":\"store\",\"endValue\":\"\",\"startValue\":\"\",\"valueType\":8,\"enableGroup\":true}]},{\"secondLogicalVal\":0,\"secondAttributeCondition\":[{\"active\":false,\"attribute\":\"mobilephone\",\"attributeSymbol\":\"startwith\",\"attributeType\":\"customerAttribute\",\"attributeValue\":\"135\",\"dataSource\":null,\"endValue\":\"\",\"startValue\":\"\",\"valueType\":2}]},{\"secondLogicalVal\":0,\"secondAttributeCondition\":[{\"active\":false,\"attribute\":\"4ccf41a0-2143-4192-9518-62c4688d4c85\",\"attributeSymbol\":\"eq\",\"attributeType\":\"categoryLabel\",\"attributeValue\":[\"100000000\"],\"dataSource\":\"labelNData\",\"endValue\":\"\",\"startValue\":\"\",\"valueType\":8,\"sqlTagId\":\"7d13ccd7-d52c-480a-b056-bf5402ddf0a5\"}]},{\"secondLogicalVal\":0,\"secondAttributeCondition\":[{\"active\":false,\"attribute\":\"8413387e-0ebb-4f20-b2f9-e9d07b71dd86\",\"attributeSymbol\":\"eq\",\"attributeType\":\"categoryLabel\",\"attributeValue\":[\"94a73354-810d-426d-bfb8-970f78804055\"],\"dataSource\":\"labelNData\",\"endValue\":\"\",\"startValue\":\"\",\"valueType\":8,\"sqlTagId\":\"fb96193c-62b3-471a-b92a-bf1703f9c9a6\"}]},{\"secondLogicalVal\":0,\"secondAttributeCondition\":[{\"active\":false,\"attribute\":\"efe86934-8d1b-45be-8dd7-4898075c7f52\",\"attributeSymbol\":\"eq\",\"attributeType\":\"categoryLabel\",\"attributeValue\":\"1\",\"dataSource\":\"labelNData\",\"endValue\":\"\",\"startValue\":\"\",\"valueType\":2}]}]},\"behaviorCondition\":{\"firstLogicalVal\":1,\"firstBehaviorCondition\":[{\"active\":false,\"eventKey\":\"$payment\",\"behavior\":{\"behaviorDate\":{\"dateType\":\"100000001\",\"limitSymbol\":\"100000004\",\"limitValue\":\"30\",\"limitUnit\":\"100000000\"},\"behaviorSymbol\":\"1\",\"behaviorValue\":\"$payment\",\"limitName\":[\"count\"],\"limitSymbol\":\"gt\",\"limitValue\":4,\"limitStartValue\":\"\",\"limitEndValue\":\"\",\"behaviorDateTime\":\"\",\"timePopoverDisabled\":false,\"radio\":\"1\",\"radio1\":\"5\",\"radio2\":\"1\",\"dateSymbol\":\"1\",\"checked\":false,\"isDisabled\":false},\"limitProperty\":true,\"limitMeasure\":true,\"attribute\":{\"secondLogicalVal\":0,\"secondBehaviorCondition\":[{\"thirdLogicalVal\":0,\"thirdBehaviorCondition\":[{\"behaviorAttribute\":\"is_member\",\"behaviorAttributeSymbol\":\"eq\",\"behaviorAttributeValue\":[\"1\"],\"valueType\":8,\"dataSource\":\"trueOrFalse\",\"attributeValue\":\"\"}]}]}},{\"active\":false,\"eventKey\":\"$order\",\"behavior\":{\"behaviorDate\":{\"dateType\":\"100000001\",\"limitSymbol\":\"100000001\",\"limitValue\":\"undefined\",\"limitUnit\":\"undefined\"},\"behaviorSymbol\":\"1\",\"behaviorValue\":\"$order\",\"limitName\":[\"total_amount\",\"beverage\"],\"limitSymbol\":\"gt\",\"limitValue\":0,\"limitStartValue\":\"\",\"limitEndValue\":\"\",\"behaviorDateTime\":\"\",\"timePopoverDisabled\":false,\"radio\":\"1\",\"radio1\":\"5\",\"radio2\":\"1\",\"dateSymbol\":\"1\",\"checked\":false,\"isDisabled\":false},\"limitProperty\":true,\"limitMeasure\":true,\"attribute\":{\"secondLogicalVal\":0,\"secondBehaviorCondition\":[{\"thirdLogicalVal\":0,\"thirdBehaviorCondition\":[{\"behaviorAttribute\":\"store\",\"behaviorAttributeSymbol\":\"belong\",\"behaviorAttributeValue\":101,\"valueType\":8,\"dataSource\":\"store\",\"attributeValue\":\"\"}]},{\"thirdLogicalVal\":0,\"thirdBehaviorCondition\":[{\"behaviorAttribute\":\"source\",\"behaviorAttributeSymbol\":\"eq\",\"behaviorAttributeValue\":[\"100000006\"],\"valueType\":8,\"dataSource\":\"orderType\",\"attributeValue\":\"\"}]}]}},{\"active\":false,\"eventKey\":\"$update\",\"behavior\":{\"behaviorDate\":{\"dateType\":\"100000001\",\"limitSymbol\":\"100000000\",\"limitValue\":\"undefined\",\"limitUnit\":\"undefined\"},\"behaviorSymbol\":\"1\",\"behaviorValue\":\"$update\",\"limitName\":[\"count\"],\"limitSymbol\":\"lte\",\"limitValue\":1,\"limitStartValue\":\"\",\"limitEndValue\":\"\",\"behaviorDateTime\":\"\",\"timePopoverDisabled\":false,\"radio\":\"1\",\"radio1\":\"5\",\"radio2\":\"1\",\"dateSymbol\":\"1\",\"checked\":false,\"isDisabled\":false},\"limitProperty\":true,\"limitMeasure\":true,\"attribute\":{\"secondLogicalVal\":0,\"secondBehaviorCondition\":[{\"thirdLogicalVal\":0,\"thirdBehaviorCondition\":[{\"behaviorAttribute\":\"status\",\"behaviorAttributeSymbol\":\"neq\",\"behaviorAttributeValue\":[\"100000002\"],\"valueType\":8,\"dataSource\":\"memberStatus\",\"attributeValue\":\"\"}]}]}},{\"active\":false,\"eventKey\":\"$signup\",\"behavior\":{\"behaviorDate\":{\"dateType\":\"100000000\",\"startDate\":\"2021-09-01 00:00:00\",\"endDate\":\"2021-09-15 23:59:59\",\"limitSymbol\":\"undefined\",\"limitValue\":\"undefined\",\"limitUnit\":\"undefined\"},\"behaviorSymbol\":\"1\",\"behaviorValue\":\"$signup\",\"limitName\":[\"count\"],\"limitSymbol\":\"eq\",\"limitValue\":1,\"limitStartValue\":\"\",\"limitEndValue\":\"\",\"behaviorDateTime\":\"\",\"timePopoverDisabled\":false,\"radio\":\"1\",\"radio1\":\"5\",\"radio2\":\"1\",\"dateSymbol\":\"1\",\"checked\":false,\"isDisabled\":false},\"limitProperty\":true,\"limitMeasure\":true,\"attribute\":{\"secondLogicalVal\":0,\"secondBehaviorCondition\":[{\"thirdLogicalVal\":0,\"thirdBehaviorCondition\":[{\"behaviorAttribute\":\"status\",\"behaviorAttributeSymbol\":\"eq\",\"behaviorAttributeValue\":[\"qw\"],\"valueType\":8,\"dataSource\":\"memberStatus\",\"attributeValue\":\"\"}]}]}}]},\"crowdCondition\":{\"firstLogicalVal\":0,\"firstCrowdCondition\":[{\"secondLogicalVal\":0,\"secondCrowdCondition\":[{\"thirdLogicalVal\":1,\"crowd\":\"611\",\"active\":false}]},{\"secondLogicalVal\":0,\"secondCrowdCondition\":[{\"thirdLogicalVal\":0,\"crowd\":\"590\",\"active\":false}]},{\"secondLogicalVal\":0,\"secondCrowdCondition\":[{\"thirdLogicalVal\":1,\"crowd\":\"594\",\"active\":false}]}]},\"logicalVal\":1}");
//
//        String sql = customerGroupCalculationService.getGroupCalculation(groupCalculation, map);
//
//        System.out.println(sql);
//
//        System.out.println(map);
//    }

    String project = "myProject";
//    String project = GlobalValue.project();

    public GroupCalculation getGroupCalculation(String json) {

        GroupCalculation groupCalculation = JSON.parseObject(json, GroupCalculation.class);

        return groupCalculation;

    }


    /**
     * @param groupCalculation 群组规则对象
     * @param param            参数
     * @return sql
     */
    public String getSql(GroupCalculation groupCalculation, Map<Integer, Object> param) {
        StringBuilder sql = new StringBuilder();
        AttributeCondition attributeCondition = groupCalculation.getAttributeCondition();
        BehaviorCondition behaviorCondition = groupCalculation.getBehaviorCondition();
        CrowdCondition crowdCondition = groupCalculation.getCrowdCondition();
        boolean appendAttribute = attributeCondition != null && attributeCondition.getFirstAttributeCondition() != null;
        boolean appendBehavior = behaviorCondition != null && behaviorCondition.getFirstBehaviorCondition() != null;
        boolean appendCrowd = crowdCondition != null && crowdCondition.getFirstCrowdCondition() != null;
        sql.append(" select cust_id  from ( ");
        if (appendAttribute) {
            sql.append(getCustomerPropertiesSql(groupCalculation.getAttributeCondition(), param));
        }
        //如果拼接人群属性  并且 拼接人群行为 或者拼接现有人群
//        sql 根据运算符 拼接 不同
        if ((appendAttribute && appendBehavior) || (appendAttribute && appendCrowd)) {
            if (groupCalculation.getLogicalVal() == 0) {
                sql.append(" INTERSECT ");
            } else {
                sql.append(" UNION ALL ");

            }
        }
        if (appendBehavior) {
            sql.append(getBehaviorSql(groupCalculation.getBehaviorCondition(), param));
        }
        //如果拼接人群行为 并且拼接现有人群
        //sql 根据运算符 拼接 不同
        if (appendBehavior && appendCrowd) {
            if (groupCalculation.getLogicalVal() == 0) {
                sql.append(" INTERSECT ");
            } else {
                sql.append(" UNION ALL ");

            }
        }
        if (appendCrowd) {
            sql.append(getCrowdSql(groupCalculation.getCrowdCondition(), param));
        }
        sql.append("  ) group by cust_id");
        return sql.toString();
    }


    /**
     * 拼接现有人群sql
     *
     * @param crowdCondition 现有人群对象
     * @param param          参数
     * @return sql
     */
    private String getCrowdSql(CrowdCondition crowdCondition, Map<Integer, Object> param) {
        List<FirstCrowdCondition> firstCrowdConditionList = crowdCondition.getFirstCrowdCondition();
        StringBuilder crowdSql = new StringBuilder();
        Integer firstLogicalVal = crowdCondition.getFirstLogicalVal();
        crowdSql.append(" select cust_id from (");
        for (int i = 0; i < firstCrowdConditionList.size(); i++) {
            //判断是否需要拼接运算符 最后一个或第一个不需要拼接
            Boolean isDiffer = firstLogicalVal == 2;
            //第一层 少拼接 一层括号
            boolean appendExists = firstCrowdConditionList.size() > 1 && i != 0 && isDiffer;
            if (appendExists) {
                crowdSql.append(" (");
            }
            crowdSql.append(" select cust_id  from (");
            crowdSql.append(getFirstCrowdConditionSql(firstCrowdConditionList.get(i), param));
            crowdSql.append(")f" + i);
            if (firstCrowdConditionList.size() > 1 && i != 0 && isDiffer) {
                //如果是多参数 并且  不是拼接第一个  并且是差集
                //如果 是多个参数并且 取交集
                crowdSql.append(" where f0.cust_id=f" + i + ".cust_id");
                crowdSql.append(" )");
            }
            if ((i + 1) < firstCrowdConditionList.size()) {
                //且 取交集
                if (firstLogicalVal == 0) {
                    crowdSql.append(" INTERSECT ");
                } else if (firstLogicalVal == 1) {
                    //取并集
                    crowdSql.append(" UNION ALL ");
                } else {
                    //取差集
                    crowdSql.append(" where  NOT  EXISTS ");

                }
            }
        }
        crowdSql.append("  )");

        return crowdSql.toString();
    }

    /**
     * 拼接第二层 现有人群 sql
     *
     * @param firstCrowdCondition 第一层
     * @param param               对象
     * @return sql
     */
    private String getFirstCrowdConditionSql(FirstCrowdCondition firstCrowdCondition, Map<Integer, Object> param) {
        StringBuilder firstCrowdConditionSql = new StringBuilder();
        List<SecondCrowdCondition> secondCrowdConditionList = firstCrowdCondition.getSecondCrowdCondition();
        Integer secondLogicalVal = firstCrowdCondition.getSecondLogicalVal();
        for (int i = 0; i < secondCrowdConditionList.size(); i++) {
            //判断是否需要拼接运算符 最后一个或第一个不需要拼接
            //差值
            Boolean isDiffer = secondLogicalVal == 2;
            boolean appendExists = secondCrowdConditionList.size() > 1 && i != 0 && isDiffer;
            if (appendExists) {
                //第一层少拼接一层括号
                firstCrowdConditionSql.append("  (");
            }
            firstCrowdConditionSql.append(" select cust_id  from (");
            firstCrowdConditionSql.append(getSecondCrowdConditionSql(secondCrowdConditionList.get(i), param));
            firstCrowdConditionSql.append(" )s" + i);
            if (secondCrowdConditionList.size() > 1 && i != 0 && isDiffer) {
                //如果 是多个参数并且 取交集
                firstCrowdConditionSql.append(" where s0.cust_id=s" + i + ".cust_id");
                firstCrowdConditionSql.append(" ) ");
            }
            if ((i + 1) < secondCrowdConditionList.size()) {
                //且 取交集
                if (secondLogicalVal == 0) {
                    firstCrowdConditionSql.append(" INTERSECT ");
                } else if (secondLogicalVal == 1) {
                    //或 取并集
                    firstCrowdConditionSql.append(" UNION ALL ");
                } else {
                    // 取差集
                    firstCrowdConditionSql.append("  where  NOT  EXISTS  ");

                }
            }
        }
        return firstCrowdConditionSql.toString();
    }

    /**
     * 拼接 第二层 现有人数 sql
     *
     * @param secondCrowdCondition 第二层对象
     * @param param                参数
     * @return sql
     */
    private String getSecondCrowdConditionSql(SecondCrowdCondition secondCrowdCondition, Map<Integer, Object> param) {
        StringBuilder secondCrowdConditionSql = new StringBuilder();
        //群组 id
        String crowd = secondCrowdCondition.getCrowd();
        if (secondCrowdCondition.getThirdLogicalVal() == 1) { // 1代表属于
            secondCrowdConditionSql.append("select cust_id from  customer_group_detail ");
            secondCrowdConditionSql.append("where group_id =? ");
            secondCrowdConditionSql.append("and batch_id = (select max(batch_id) from customer_group_detail where project = '" + project + "' and group_id = ");
            secondCrowdConditionSql.append(" '" + crowd + "'");
            secondCrowdConditionSql.append(" )");
        } else { // 不属于
            secondCrowdConditionSql.append("select e1.customer_id cust_id from customer e1  ");
            secondCrowdConditionSql.append("where project = '" + project + "'  and not exists ( select 1 from customer_group_detail e2 where group_id = ? ");
            secondCrowdConditionSql.append("and batch_id=  (select max(batch_id) from customer_group_detail where project = '" + project + "' and group_id = ");
            secondCrowdConditionSql.append(" '" + crowd + "'");
            secondCrowdConditionSql.append(" )");
            secondCrowdConditionSql.append("and e1.customer_id = e2.cust_id)");
        }
        param.put((param.size() + 1), crowd);
        return secondCrowdConditionSql.toString();
    }


    /**
     * 获得人群属性sql
     *
     * @param attributeCondition 人群属性
     * @param param              参数
     * @return sql
     */
    private String getCustomerPropertiesSql(AttributeCondition attributeCondition, Map param) {
        StringBuilder firstSql = new StringBuilder();
        //第一层 规则
        List<FirstAttributeCondition> firstAttributeConditionList = attributeCondition.getFirstAttributeCondition();
        Integer firstLogicalVal = attributeCondition.getFirstLogicalVal();
        firstSql.append("select cust_id  from (");
        //拼接每一层的数据
        for (int i = 0; i < firstAttributeConditionList.size(); i++) {
            //第二层数据
            FirstAttributeCondition firstAttributeCondition = firstAttributeConditionList.get(i);
            List<SecondAttributeCondition> secondAttributeCondition = firstAttributeCondition.getSecondAttributeCondition();
            firstSql.append(" ( ");
            firstSql.append(getSecondAttributeSql(secondAttributeCondition, firstAttributeCondition.getSecondLogicalVal(), param));
            firstSql.append(" )");
            //最后一次不拼接 运算符
            if ((i + 1) < firstAttributeConditionList.size()) {
                //且 取交集
                if (firstLogicalVal == 0) {
                    firstSql.append(" INTERSECT ");
                } else {
                    firstSql.append(" UNION ALL ");

                }
            }
        }
        firstSql.append(" ) ");
        return firstSql.toString();
    }

    /**
     * 拼接人群属性解析第二层
     *
     * @param secondAttributeCondition 人群属性
     * @param secondLogicalVal         0且  1或
     * @param param                    值
     */
    @SuppressWarnings("all")
    private String getSecondAttributeSql(List<SecondAttributeCondition> secondAttributeCondition, Integer secondLogicalVal, Map param) {
        String customerAttributeType = "customerAttribute";
        String valueLabel = "valueLabel";
        String categoryLabel = "categoryLabel";
        StringBuilder attributeConditionSql = new StringBuilder();
        //将人员属性的 参数拿出 单独拼接 需要拼接 and
        List<SecondAttributeCondition> customerPropertiesList = new ArrayList<>();
        for (SecondAttributeCondition attributeCondition : secondAttributeCondition) {
            if (customerAttributeType.equals(attributeCondition.getAttributeType())) {
                customerPropertiesList.add(attributeCondition);
            }
        }
        //拼接人员属性sql
        StringBuilder attributeConditionCustomerPropertiesSql = new StringBuilder();
        attributeConditionCustomerPropertiesSql.append("  select  c.customer_id cust_id from customer c ");
        attributeConditionCustomerPropertiesSql.append("   where c.project = '" + project + "'");
        attributeConditionCustomerPropertiesSql.append(" and ( ");
        for (int i = 0; i < customerPropertiesList.size(); i++) {
            SecondAttributeCondition attributeCondition = customerPropertiesList.get(i);
            attributeConditionCustomerPropertiesSql.append(getAttributeConditionCustomerPropertiesSql(attributeCondition, secondLogicalVal, param, i));
        }
        attributeConditionCustomerPropertiesSql.append(")");
        if (customerPropertiesList.size() > 0) {
            //拼接人员属性sql
            attributeConditionSql.append(attributeConditionCustomerPropertiesSql);
        }
        //拼接标签sql
        for (int i = 0; i < secondAttributeCondition.size(); i++) {
            SecondAttributeCondition attributeCondition = secondAttributeCondition.get(i);
            if (!customerAttributeType.equals(attributeCondition.getAttributeType())) {
                //属性key
                //如果人员属性list有数据 或者 遍历的不是第一个说明上面已经拼接了sql
                if (i > 0 || customerPropertiesList.size() > 0) {
                    if (secondLogicalVal == 0) {
                        attributeConditionSql.append(" INTERSECT ");
                    } else {
                        attributeConditionSql.append(" UNION ALL ");
                    }
                }
                // todo tag_id对应 attribute tag_rule_id对应 attributeValue
                //分类标签
                if (categoryLabel.equals(attributeCondition.getAttributeType())) {
                    //拼接 in语句 tagId为参数
                    StringBuilder tagId = new StringBuilder();
                    String attribute = null;
                    String tagRuleId = "";
                    if (!(attributeCondition.getAttributeValue() instanceof String)) {
                        List split = (List) attributeCondition.getAttributeValue();
                        for (int j = 0; j < split.size(); j++) {
                            tagId.append("'" + split.get(j) + "'");
                            if (j < (split.size() - 1)) {
                                tagId.append(",");
                            }
                        }
                        attribute = attributeCondition.getAttribute();
                    } else {
                        Object attributeValue = attributeCondition.getAttributeValue();
                        tagId.append("'" + attributeValue.toString() + "'");
//                        List<TagRuleDto> tagRuleDtos = tagRuleMapper.queryByTagId(attributeCondition.getAttribute());
//                        tagRuleId = tagRuleDtos.get(0).getId();
                        //todo 需要传入id
                        String id = "thisIsId1";
                        tagRuleId = id;
                    }

                    if (attributeCondition.getSqlTagId() != null) {  // sql维度标签和偏好

                        attributeConditionSql.append(" ( select  c.customer_id as cust_id from customer_tag_result c ");
                        //laiql - 2020/12/22 ----> 修复 project值缺少 ''
                        attributeConditionSql.append("   where  c.project =  '" + project + "'  and c.batch_id = (select max(batch_id) from customer_tag_result where project = '" + project + "' and tag_rule_id = ");
                        attributeConditionSql.append(" '" + attributeCondition.getSqlTagId() + "'");
                        attributeConditionSql.append(" ) and c.tag_rule_id  = ");
                        attributeConditionSql.append(" '" + attributeCondition.getSqlTagId() + "'");
                        attributeConditionSql.append(" and tag_value in ( ");
                        attributeConditionSql.append(tagId);
                        attributeConditionSql.append(" )");
                        attributeConditionSql.append("   )");
                    } else {  // 普通分类标签(静态与自定义)
                        attributeConditionSql.append(" ( select  c.customer_id as cust_id from customer_tag_result c ");
                        //laiql - 2020/12/22 ----> 修复 project值缺少 ''
                        attributeConditionSql.append("   where  c.project =  '" + project + "'  and c.batch_id in (select max(batch_id) from customer_tag_result where project = '" + project + "' and tag_id = ");
                        attributeConditionSql.append("'" + attribute + "'");
                        attributeConditionSql.append(" ) and c.tag_rule_id  in ");
                        attributeConditionSql.append("(" + tagId + ")");
                        attributeConditionSql.append("   )");
                    }

                } else {
                    //值标签
                    attributeConditionSql.append(getAttributeConditionTagValueSql(attributeCondition, param));
                }
            }
        }
        return attributeConditionSql.toString();
    }

    /**
     * 拼接 顾客属性sql
     *
     * @param attributeCondition 对象
     * @param secondLogicalVal   运算符
     * @param param              参数
     * @param index              是否需要拼接 and 或or 如果index=1说明sql不需要拼接连接符
     *                           sql样式为 project=xxx and( ${properties}=xxx )
     *                           >1说明需要拼接连接符
     * @return sql
     */
    private String getAttributeConditionCustomerPropertiesSql(SecondAttributeCondition attributeCondition, Integer secondLogicalVal, Map param, int index) {
        StringBuffer customerSql = new StringBuffer();
        //查询字段
        String valueParam = attributeCondition.getAttribute();
        String dataSourceStr = attributeCondition.getDataSource();
        //字段连接符 如果只有一个参数 不拼接 sql 为 project=xxx and  (xxx=xxx)
        //如果有以上参数 sql 为project=xxx and  (xxx=xxx and 或者or xxx=xxx)
        String connect = "";
        if (index > 0) {
            //0且  1或
            if (secondLogicalVal == 0) {
                connect = " and ";
            } else {
                connect = " or ";
            }
        }
        customerSql.append(getAttributeSecondSql(param, valueParam, attributeCondition, connect, dataSourceStr));
        return customerSql.toString();
    }

    /**
     * 拼接tagValue sql
     *
     * @param attributeCondition 对象
     * @param param              参数
     * @return sql
     */
    @SuppressWarnings("all")
    private String getAttributeConditionTagValueSql(SecondAttributeCondition attributeCondition, Map param) {
        StringBuilder tagValueSql = new StringBuilder();
        String attribute = attributeCondition.getAttribute();
        // 根据二级标签的id查询三级标签的id
//        List<TagRuleDto> tagRuleDtos = tagRuleMapper.queryByTagId(attribute);
        // 值标签为sql标签，只存在一条
//        String id = tagRuleDtos.get(0).getId();
        //todo 需要传入id
        String id = "thisIsId2";
        //值  当值类型为8时 值为数组
        Object attributeValue = attributeCondition.getAttributeValue();
        //数据源
        String datasource = attributeCondition.getDataSource();
        //值类型 为8时 用in的方式
        Integer valueType = attributeCondition.getValueType();
        //数值标签
        tagValueSql.append("   ( select  c.customer_id as cust_id from customer_tag_result c ");
//        tagValueSql.append("   where c.tag_rule_id = '" + id + "'");
        tagValueSql.append("   where c.tag_id = '" + attribute + "'");
        param.put(param.size() + 1, id);
        //客户id 不为空的
        tagValueSql.append(" and c.customer_id is not null ");
        tagValueSql.append(" and c.project = '" + project + "'");
//        tagValueSql.append(" and c.batch_id = (select max(batch_id) from customer_tag_result where project = '" + project + "' and tag_rule_id = '" + id + "')");
        tagValueSql.append(" and c.batch_id = (select max(batch_id) from customer_tag_result where project = '" + project + "' and tag_id = '" + attribute + "')");
        param.put(param.size() + 1, id);
        String valueParam = " tag_value ";
        tagValueSql.append(getAttributeSecondSql(param, valueParam, attributeCondition, " and", datasource));
        tagValueSql.append(" )");

        return tagValueSql.toString();
    }

    /**
     * 第二层sql 拼接
     *
     * @param param              参数
     * @param valueParam         数据库查询字段
     * @param attributeCondition 属性
     * @param connect            字段直接连接  and 或 or
     * @param dataSourceStr      数据源 维度组中的dimkey
     * @return sql
     */
    @SuppressWarnings("all")
    private String getAttributeSecondSql(Map param, String valueParam, SecondAttributeCondition attributeCondition, String connect, String dataSourceStr) {
        String belongStr = "belong";
        String notBelongStr = "notBelong";
        StringBuilder tagValueSql = new StringBuilder();
        //值
        Object attributeValue = attributeCondition.getAttributeValue();
        //值类型
        Integer valueType = attributeCondition.getValueType();
        //运算符
        String attributeSymbol = attributeCondition.getAttributeSymbol();
        //修改后的运算符
        String op = getOperation(attributeSymbol);
        if (belongStr.equals(attributeSymbol) || notBelongStr.equals(attributeSymbol)) {
            String belongOp = belongStr.equals(attributeSymbol) ? "EXISTS" : " not EXISTS";
//            List split = (List) attributeValue;
//            StringBuilder groupIdS = new StringBuilder();
//            for (int i = 0; i < split.size(); i++) {
//                groupIdS.append("'" + split.get(i) + "'");
//                if (i < (split.size() - 1)) {
//                    groupIdS.append(",");
//                }
//            }
            //属于单独拼接
            tagValueSql.append("" + connect + " " + belongOp + "  ( select 1 from dimdatagroupdetail dt where  dt.project='" + project + "' ");
            tagValueSql.append(" and   dt.dimkey= '" + dataSourceStr + "' and group_id ='" + attributeValue + "'" +
//            tagValueSql.append(" and   dt.dimkey= '" + dataSourceStr + "' and group_id in(" + groupIdS + ") " +
                    " and c." + valueParam + "=dt.key");
            tagValueSql.append(" )");

        } else if (GroupCalculationValueTypeEnum.DATE.getCode().equals(valueType)) {
            //日期格式
            tagValueSql.append(" " + connect + "  date_diff('day',cast( " + valueParam + " as date),cast('" + attributeValue + "' as date))" + op + "0");
//            param.put(param.size() + 1, attributeValue);
        } else if (GroupCalculationValueTypeEnum.DATETIME.getCode().equals(valueType)) {
            if (StringUtils.isNotBlank(op)) {
                tagValueSql.append(" " + connect + "  cast(" + valueParam + "  as timestamp) " + op + "cast('" + attributeValue + "' as timestamp)");
//                param.put(param.size() + 1, attributeValue);
            } else {
                //特殊时间判断
                tagValueSql.append(getDateTimeSql(attributeSymbol, attributeValue, valueParam, connect));
            }
        } else if (GroupCalculationValueTypeEnum.INT.getCode().equals(valueType)) {
            String betweenStr = "between";
            //整形
            //介于
            if (betweenStr.equals(attributeSymbol)) {
                String[] split = attributeValue.toString().split(",");
                String num1 = split[0];
                String num2 = split[1];
                //如果前面的值 比后面的值 大  反转
                if (Integer.valueOf(num1) > Integer.valueOf(num2)) {
                    String num3 = num1;
                    num1 = num2;
                    num2 = num3;
                }
                //判断 大小
                tagValueSql.append(" " + connect + " ( cast( " + valueParam + "  as int ) >= cast(" + num1 + " as int)");
                tagValueSql.append(" and cast( " + valueParam + "  as int ) <= cast(" + num2 + " as int) )");
            } else {
                tagValueSql.append(" " + connect + "  cast( " + valueParam + "  as int ) " + op + "  cast( " + attributeValue + " as int)");
                param.put(param.size() + 1, attributeValue);

            }
        } else if (GroupCalculationValueTypeEnum.DECIMAL.getCode().equals(valueType)) {
            //Double
            //介于
            String betweenStr = "between";
            if (betweenStr.equals(attributeSymbol)) {
                String[] split = attributeValue.toString().split(",");
                String num1 = split[0];
                String num2 = split[1];
                //如果前面的值 比后面的值 大  反转
                if (Double.valueOf(num1) > Double.valueOf(num2)) {
                    String num3 = num1;
                    num1 = num2;
                    num2 = num3;
                }
                //判断 大小
                tagValueSql.append(" " + connect + " ( cast( " + valueParam + "  as double ) >= cast(" + num1 + " as double)");
                tagValueSql.append(" and cast( " + valueParam + "  as double ) <= cast(" + num2 + " as double) )");
            } else {
                //浮点 货币
                tagValueSql.append(" " + connect + "  cast(" + valueParam + "  as double ) " + op + "  cast(" + attributeValue + " as double)");
                param.put(param.size() + 1, attributeValue);
            }
        } else if (GroupCalculationValueTypeEnum.DIMENSION_LIST.getCode().equals(valueType)
                || GroupCalculationValueTypeEnum.DIMENSION.getCode().equals(valueType)
                || GroupCalculationValueTypeEnum.DIMENSION_MONTH.getCode().equals(valueType)
        ) {//如果为空
            if ("null".equals(attributeSymbol)) {
                tagValueSql.append(" " + connect + " " + valueParam + "    is null");
            } else if ("notnull".equals(attributeSymbol)) {
                tagValueSql.append(" " + connect + " " + valueParam + "    is not null");
            } else if ("eqCurrentMonth".equals(attributeSymbol)) {
                tagValueSql.append(" " + connect + " cast(" + valueParam + " as string) " + " = " + " cast('" + getCurrentMonth(attributeCondition.getIsEncript()) + "' as string)");
            } else {
                //查询类型
                //查询类型 字段类型 并且为多个 用in not in的方式
                StringBuilder strValue = new StringBuilder();
                List split = (List) attributeValue;
                for (int i = 0; i < split.size(); i++) {
                    strValue.append("'" + split.get(i) + "'");
                    if (i < (split.size() - 1)) {
                        strValue.append(",");
                    }
                }
                if (" = ".equals(op)) {
                    tagValueSql.append(" " + connect + " " + valueParam + "  in ( " + strValue + " )");
                } else if (" != ".equals(op)) {
                    tagValueSql.append(" " + connect + " " + valueParam + " not in ( " + strValue + " )");
                }
            }
        } else {
            //如果为空
            if ("null".equals(attributeSymbol)) {
                tagValueSql.append(" " + connect + " " + valueParam + "    is null");
            } else if ("notnull".equals(attributeSymbol)) {
                tagValueSql.append(" " + connect + " " + valueParam + "    is not null");
            } else {
                String tmp;
                if ("contains".equals(attributeSymbol) || "notcontains".equals(attributeSymbol)) {
                    //like '% value %'  not like
                    param.put(param.size() + 1, "%" + attributeValue + "%");
                    tmp = "'%" + attributeValue + "%'";
                } else if ("startwith".equals(attributeSymbol)) {
                    //like 'value %'
                    param.put(param.size() + 1, attributeValue + "%");
                    tmp = "'" + attributeValue + "%'";
                } else if ("endwith".equals(attributeSymbol)) {
                    //like '% value '
                    param.put(param.size() + 1, "%" + attributeValue);
                    tmp = "'%" + attributeValue + "'";
                } else {
                    param.put(param.size() + 1, attributeValue);
                    tmp = "'" + attributeValue + "'";
                }
                tagValueSql.append(" " + connect + " cast(" + valueParam + " as string) " + op + "cast(" + tmp + " as string)");
            }
        }
        return tagValueSql.toString();
    }

    public String getCurrentMonth(boolean isEncript) {
        String currentMonth = DatesUtil.getCurrentMonth();
        if (isEncript) {
            try {
                currentMonth = AesUtils.encryptStr(currentMonth, ApplicationConfig.AES_KEY());
            } catch (Exception e) {
                e.printStackTrace();
//                log.error("currentMonth encript error: {}", e);
            }
        }
        return currentMonth;
    }

    /**
     * 获取条件运算符
     *
     * @param op 运算符字符串
     * @return 运算符
     */
    @SuppressWarnings("all")
    private String getOperation(String op) {
        String operation = "";
        if ("eq".equals(op)) {
            operation = " = ";
        } else if ("neq".equals(op)) {
            operation = " != ";
        } else if ("gt".equals(op)) {
            operation = " > ";
        } else if ("lt".equals(op)) {
            operation = " < ";
        } else if ("gte".equals(op)) {
            operation = " >= ";
        } else if ("lte".equals(op)) {
            operation = " <= ";
        } else if ("startwith".equals(op) || "endwith".equals(op) || "contains".equals(op)) {
            //包含 或者 开头等于 结束等于
            operation = " like ";
        } else if ("notcontains".equals(op)) {
            //不包含
            operation = " not like ";
        } else if ("belong".equals(op)) {
            //属于
            operation = " EXISTS ";
        } else if ("notBelong".equals(op)) {
            //不属于
            operation = " not EXISTS";
        }
        return operation;
    }

    /**
     * tag拼接日期操作sql
     * 类型是时间戳对比
     *
     * @param logicalVal  操作符
     * @param selectValue 值
     * @param field       查询字段
     * @param connect     查询字段连接符 and 或 or
     * @return sql
     */
    @SuppressWarnings("all ")
    private String getDateTimeSql(String logicalVal, Object selectValue, String field, String connect) {
        StringBuilder sql = new StringBuilder();
        String yesterdayStr = "yesterday";
        String todayStr = "today";
        String lastWeekStr = "lastweek";
        String thisWeekStr = "thisweek";
        String lastMonthStr = "lastmonth";
        String thisMonthStr = "thismonth";
        String lastYearStr = "lastyear";
        String thisYearStr = "thisyear";
        String recentlyDayStr = "recentlyday";
        String recentlyWeekStr = "recentlyweek";
        String recentlyMonthStr = "recentlymonth";
        String recentlyYearStr = "recentlyyear";
        String betweenStr = "between";
        String value = "";
        String startValue = "";
        String endValue = "";
        try {
            if (!logicalVal.equals(betweenStr)) {
                value = selectValue + "";
            } else {
                //增加时间判断--- update-laiql
                if (selectValue instanceof String && ((String) selectValue).contains(",")) {
                    List<String> dateValue = Arrays.asList(((String) selectValue).split(","));
                    startValue = dateValue.get(0);
                    endValue = dateValue.get(1);
                } else if (selectValue instanceof List) {
                    List<String> dateValue = (List) selectValue;
                    startValue = dateValue.get(0);
                    endValue = dateValue.get(1);
                }

            }
            SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            SimpleDateFormat valueDf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss SSS");
            Calendar calendar = Calendar.getInstance();
            //修复时间戳转换问题----update--laiql
            if (logicalVal.equals(yesterdayStr)) {
                // 昨天
                calendar.add(Calendar.DATE, -1);
                Date time = calendar.getTime();
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(df.format(time) + " 00:00:00 000").getTime() + " <cast( " + field + " as bigint) ");
//                sql.append(" and  cast ( " + valueDf.parse(df.format(time) + " 23:59:59 999").getTime() + " > cast(  " + field + " as bigint)  )");
                //修复时间戳转换begint错误---update---laiql
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(df.format(time) + " 00:00:00 000").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp)) ");
                sql.append(" and  cast ( " + valueDf.parse(df.format(time) + " 23:59:59 999").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(todayStr)) {
                //今天
                calendar.setTime(new Date());
                Date time = calendar.getTime();
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(df.format(time) + " 00:00:00 000").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + valueDf.parse(df.format(time) + " 23:59:59 999").getTime() + " > cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(df.format(time) + " 00:00:00 000").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + valueDf.parse(df.format(time) + " 23:59:59 999").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(lastWeekStr)) {
                //上周
                String[] split = GetDateUtils.getLastWeekTimeInterval(df).split(",");
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() + "  < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 23:59:59 999").getTime() + " > cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() / 1000 + "  as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 23:59:59 999").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(thisWeekStr)) {
                //本周
                String[] split = GetDateUtils.getThisWeekTimeInterval(df).split(",");
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() + "< cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 23:59:59 999").getTime() + " > cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() / 1000 + "as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 23:59:59 999").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(lastMonthStr)) {
                //上个月
                String[] split = GetDateUtils.getLastMonth(df).split(",");
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 00:00:00 000").getTime() + "> cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 00:00:00 000").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(thisMonthStr)) {
                //本月
                String[] split = GetDateUtils.getThisMonth(df).split(",");
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 00:00:00 000").getTime() + " > cast(" + field + " as bigint) ) ");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(split[0] + " 00:00:00 000").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + valueDf.parse(split[1] + " 00:00:00 000").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) ) ");
            } else if (logicalVal.equals(lastYearStr)) {
                //去年
                df = new SimpleDateFormat("yyyy");
                calendar = Calendar.getInstance();
                calendar.add(Calendar.YEAR, -1);
                String lastYear = df.format(calendar.getTime());
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(lastYear + "-01-01 00:00:00 000").getTime() + "  < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + valueDf.parse(lastYear + "-12-31 23:59:59 999").getTime() + " > cast(" + field + " as bigint) ) ");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(lastYear + "-01-01 00:00:00 000").getTime() / 1000 + "  as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + valueDf.parse(lastYear + "-12-31 23:59:59 999").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) ) ");
            } else if (logicalVal.equals(thisYearStr)) {
                //今年
                df = new SimpleDateFormat("yyyy");
                String thisYear = df.format(new Date());
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(thisYear + "-01-01 00:00:00 000").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + valueDf.parse(thisYear + "-12-31 23:59:59 999").getTime() + " > cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(thisYear + "-01-01 00:00:00 000").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + valueDf.parse(thisYear + "-12-31 23:59:59 999").getTime() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(recentlyDayStr)) {
                //最近X天
                calendar = Calendar.getInstance();
                calendar.add(Calendar.DATE, -(Integer.valueOf(value) - 1));
                String date = df.format(calendar.getTime());
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + System.currentTimeMillis() + " > cast(" + field + " as bigint) ) ");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + System.currentTimeMillis() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) ) ");
            } else if (logicalVal.equals(recentlyWeekStr)) {
                //最近X周
                calendar = Calendar.getInstance();
                calendar.add(Calendar.DATE, -((Integer.valueOf(value) * 7) - 1));
                String date = df.format(calendar.getTime());
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + System.currentTimeMillis() + "> cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + System.currentTimeMillis() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(recentlyMonthStr)) {
                //最近X月
                calendar = Calendar.getInstance();
                calendar.add(Calendar.MONTH, -(Integer.valueOf(value)));
                String date = df.format(calendar.getTime());
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + System.currentTimeMillis() + " > cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + System.currentTimeMillis() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(recentlyYearStr)) {
                //最近X年
                calendar = Calendar.getInstance();
                calendar.add(Calendar.YEAR, -(Integer.valueOf(value)));
                String date = df.format(calendar.getTime());
//                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() + " < cast(" + field + " as bigint)");
//                sql.append(" and  cast ( " + System.currentTimeMillis() + " > cast(" + field + " as bigint) )");
                sql.append(" " + connect + " (  cast ( " + valueDf.parse(date + " 00:00:00 000 ").getTime() / 1000 + " as timestamp) < (cast ( " + field + " as timestamp))");
                sql.append(" and  cast ( " + System.currentTimeMillis() / 1000 + " as timestamp) > (cast ( " + field + " as timestamp)) )");
            } else if (logicalVal.equals(betweenStr)) {
                //介于  双日期
                sql.append(" " + connect + " ( cast( " + field + " as  timestamp) >=  cast('" + startValue + "'  as timestamp )");
                sql.append(" and  cast( " + field + "  as timestamp) <= cast('" + endValue + "' as timestamp)  )");
            }
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return sql.toString();
    }

    /**
     * 获得 人群行为
     *
     * @param behaviorCondition 人群行为
     * @param param             参数
     * @return sql
     */
    private String getBehaviorSql(BehaviorCondition behaviorCondition, Map<Integer, Object> param) {
        StringBuilder firstSql = new StringBuilder();
        //第一层 规则
        List<FirstBehaviorCondition> firstBehaviorConditionList = behaviorCondition.getFirstBehaviorCondition();
        Integer firstLogicalVal = behaviorCondition.getFirstLogicalVal();
        //如果进行统计逻辑 拼接 统计sql  ,不进行拼接空串
        firstSql.append("select  cust_id   from  (");
        for (int i = 0; i < firstBehaviorConditionList.size(); i++) {
            String limitName = "";
            String limitTableName = "";
            FirstBehaviorCondition firstBehaviorCondition = firstBehaviorConditionList.get(i);
            Behavior behavior = firstBehaviorCondition.getBehavior();
            Object behaviorDateObj = behavior.getBehaviorDate();
            List<String> behaviorDate = null;
            HashMap<String, String> behaviorDateNew = null;
            String simpleName = behaviorDateObj.getClass().getSimpleName();
            if (behaviorDateObj instanceof List) {
                behaviorDate = (List) behavior.getBehaviorDate();
            } else if (behaviorDateObj instanceof Map) { // map表示新的结构
                if (simpleName.equals("JSONObject")) {
                    JSONObject behaviorDateObj1 = (JSONObject) behaviorDateObj;
                    behaviorDateNew = JSONObject.parseObject(behaviorDateObj1.toJSONString(), new TypeReference<HashMap<String, String>>() {
                    });
                } else if (simpleName.equals("LinkedHashMap")) {
                    behaviorDateNew = (HashMap<String, String>) behaviorDateObj;
                }
            } else {
                //相对日期 日期是以逗号分割的字符串
                String behaviorDateStr = behaviorDateObj.toString();
                behaviorDate = Arrays.asList(behaviorDateStr.split(","));
            }
            //BUG    下面也拼接了  日期条件  导致全部被删 需要修改
            //有过 还是 没有
            Integer behaviorSymbol = behavior.getBehaviorSymbol();
            //如果查找的是没有  用not exists  排除  下面拼接正常的sql里面的数据
            if (behaviorSymbol == 2) {
                firstSql.append("select  customer_id cust_id   from  customer r1 ");
                firstSql.append(" where  project = '" + project + "'");
                //是会员
                firstSql.append(" and  is_member = '1'");
//                firstSql.append(" and  event_key =?");
//                param.put(param.size() + 1, firstBehaviorCondition.getEventKey());
                //拼接时间sql
//                getEventTimeSql(param, firstSql, behaviorDate);
                firstSql.append("  and  not exists( select 1 from (");
            }
            //如果拼接 运算操作  累计满足
            if (firstBehaviorCondition.getLimitMeasure()) {
                limitTableName = "li" + i;
                //得到 对应运算符
                limitName = "," + getlimitName((List) behavior.getLimitName()) + " as   limitName";
                firstSql.append(" select " + limitTableName + ".cust_id  from ( ");

            }
            //正常的拼接sql
            firstSql.append("select  customer_id cust_id " + limitName + "  from  retailevent c  where 1=1 ");
            firstSql.append(" and  project = '" + project + "'");
            firstSql.append(" and  event_key = '" + firstBehaviorCondition.getEventKey() + "'");
            firstSql.append(" and  customer_id !='' ");
            param.put(param.size() + 1, firstBehaviorCondition.getEventKey());
            //拼接时间sql
            if (behaviorDate != null) {
                getEventTimeSql(param, firstSql, behaviorDate);
            } else if (behaviorDateNew != null) {
                getEventTimeSql(param, firstSql, behaviorDateNew);
            }


            //是否拼接人员行为属性
            if (firstBehaviorCondition.getLimitProperty()) {
                firstSql.append(" and (  ");
                //获得第二层对象 sql
                firstSql.append(getSecondBehaviorConditionSql(firstBehaviorCondition.getAttribute(), param));
                firstSql.append("  ) ");
            }
            //拼接排除sql   拼接not exists  对比条件
            if (behaviorSymbol == 2) {
                firstSql.append(" ) r2  where r1.customer_id=r2.cust_id )");
            }
            //如果拼接 运算操作
            if (firstBehaviorCondition.getLimitMeasure()) {
                String betweenStr = "between";
                String LimitSymbol = getOperation(behavior.getLimitSymbol());
                firstSql.append("    group by customer_id ");
                firstSql.append(" ) " + limitTableName + " where 1=1 ");
                //介于
                if (betweenStr.equals(behavior.getLimitSymbol())) {
                    String[] split = behavior.getLimitValue().toString().split(",");
                    String num1 = split[0];
                    String num2 = split[1];
                    //如果前面的值 比后面的值 大  反转
                    if (Integer.valueOf(num1) > Integer.valueOf(num2)) {
                        String num3 = num1;
                        num1 = num2;
                        num2 = num3;
                    }
                    //判断 大小
                    firstSql.append(" and cast(" + limitTableName + ".limitName as integer) >= cast( " + num1 + " as integer)");
                    firstSql.append(" and cast(" + limitTableName + ".limitName as integer) <= cast( " + num2 + " as integer)");
                } else {
                    //整形dimension
                    firstSql.append(" and cast(" + limitTableName + ".limitName as integer)");
                    firstSql.append(LimitSymbol + " cast( " + behavior.getLimitValue() + " as integer)");
                    param.put(param.size() + 1, behavior.getLimitValue());
                }


            }
            if ((i + 1) < firstBehaviorConditionList.size()) {
                //且 取交集
                if (firstLogicalVal == 0) {
                    firstSql.append(" INTERSECT ");
                } else {
                    firstSql.append(" UNION ALL ");

                }
            }
        }
        firstSql.append(" ) ");
        return firstSql.toString();
    }

    private void getEventTimeSql(Map<Integer, Object> param, StringBuilder firstSql, List<String> behaviorDate) {
        if (behaviorDate.size() > 1) {
            appendEventTime(firstSql, behaviorDate);
            firstSql.append(" and  cast(event_month as string)>=cast('" + getMonth(behaviorDate.get(0)) + "' as string)");
            param.put(param.size() + 1, getMonth(behaviorDate.get(0)));
            firstSql.append(" and  cast(event_month as string)<=cast('" + getMonth(behaviorDate.get(1)) + "' as string)");
            param.put(param.size() + 1, getMonth(behaviorDate.get(1)));

        } else {
            firstSql.append(" and  cast(event_time as timestamp)=cast('" + behaviorDate.get(0) + "' as timestamp)");
        }
    }

    private void getEventTimeSql(Map<Integer, Object> param, StringBuilder firstSql, HashMap<String, String> behaviorDate) {
        if (behaviorDate.size() > 1) {
            switch (Integer.parseInt(behaviorDate.get("dateType"))) {
                case 100000000:   // 具体时间走旧的拼接方式
                    List<String> dateList = new ArrayList<>();
                    dateList.add(behaviorDate.get("startDate"));
                    dateList.add(behaviorDate.get("endDate"));
                    appendEventTime(firstSql, dateList);
                    firstSql.append(" and  cast(event_month as string)>=cast('" + getMonth(behaviorDate.get("startDate")) + "' as string)");
                    param.put(param.size() + 1, getMonth(behaviorDate.get("startDate")));
                    firstSql.append(" and  cast(event_month as string)<=cast('" + getMonth(behaviorDate.get("endDate")) + "' as string)");
                    param.put(param.size() + 1, getMonth(behaviorDate.get("endDate")));
                    break;

                case 100000001: // 动态时间拼接 动态一定存在limitSymbol
                    // 拼接动态时间
                    appendEventTime(firstSql, behaviorDate);
                    break;
            }
        } /*else {
            firstSql.append(" and  cast(event_time as timestamp)=cast('" + behaviorDate.get(0) + "' as timestamp)");
        }*/
    }

    /**
     * 拼接动态时间
     *
     * @param firstSql     返回的sql
     * @param behaviorDate 时间集合
     */
    private void appendEventTime(StringBuilder firstSql, HashMap<String, String> behaviorDate) {
        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        String nowDate = dateFormat.format(new Date()); // 获取当前时间
        switch (behaviorDate.get("limitSymbol")) {
            case "100000000": // 至今（时间小于当前时间）
                // 获取当前时间
                firstSql.append(" and  cast(event_time as timestamp) <= cast('" + nowDate + "' as timestamp)");
                break;
            case "100000001": // 本周（周天到当前时间）
                String beginDayOfWeek = DatesUtil.getBeginDayOfWeek();
                firstSql.append(" and  cast(event_time as timestamp)>=cast('" + beginDayOfWeek + "' as timestamp)");
                firstSql.append(" and  cast(event_time as timestamp)<=cast('" + nowDate + "' as timestamp)");
                break;
            case "100000002": // 本月
                String beginDayOfMonth = DatesUtil.getBeginDayOfMonth();
                firstSql.append(" and  cast(event_time as timestamp)>=cast('" + beginDayOfMonth + "' as timestamp)");
                firstSql.append(" and  cast(event_time as timestamp)<=cast('" + nowDate + "' as timestamp)");
                break;
            case "100000003": // 本年
                String beginDayOfYear = DatesUtil.getBeginDayOfYear();
                firstSql.append(" and  cast(event_time as timestamp)>=cast('" + beginDayOfYear + "' as timestamp)");
                firstSql.append(" and  cast(event_time as timestamp)<=cast('" + nowDate + "' as timestamp)");
                break;
            case "100000004": // 过去多少天/周/月/年
                appendEventTimeForXXTime(firstSql, behaviorDate);
                firstSql.append(" and  cast(event_time as timestamp)<=cast('" + nowDate + "' as timestamp)");
                break;
            case "100000005": // 今天之前
                String today = DatesUtil.getToday();
                firstSql.append(" and  cast(event_time as timestamp) <= cast('" + today + "' as timestamp)");
                break;
            default:
                System.out.println("945行出错");
                ;
        }
    }

    private void appendEventTimeForXXTime(StringBuilder firstSql, HashMap<String, String> behaviorDate) {
        Integer limitValue = Integer.parseInt(behaviorDate.get("limitValue"));// 获取度量值
        switch (behaviorDate.get("limitUnit")) {
            case "100000000":
                String frontDay = DatesUtil.getFrontDay(limitValue);
                firstSql.append(" and  cast(event_time as timestamp)>=cast('" + frontDay + "' as timestamp)");
                break;
            case "100000001":
                String frontweek = DatesUtil.getFrontweek(limitValue);
                firstSql.append(" and  cast(event_time as timestamp)>=cast('" + frontweek + "' as timestamp)");
                break;
            case "100000002":
                String frontMonth = DatesUtil.getFrontMonth(limitValue);
                firstSql.append(" and  cast(event_time as timestamp)>=cast('" + frontMonth + "' as timestamp)");
                break;
            case "100000003":
                String frontYear = DatesUtil.getFrontYear(limitValue);
                firstSql.append(" and  cast(event_time as timestamp)>=cast('" + frontYear + "' as timestamp)");
                break;
        }
    }

    private void appendEventTime(StringBuilder firstSql, List<String> behaviorDate) {
        String beginTime = behaviorDate.get(0);
        String endTime = behaviorDate.get(1);

        SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {
            Date parse = dateFormat.parse(beginTime);
            firstSql.append(" and  cast(event_time as timestamp)>=cast('" + beginTime + "' as timestamp)");
        } catch (ParseException e) {
            System.out.println("出错了：" + e);
            ;
            firstSql.append(" and  cast(event_time as date)>=cast('" + behaviorDate.get(0) + "' as date)");
        }

        try {
            dateFormat.parse(endTime);
            firstSql.append(" and  cast(event_time as timestamp)<=cast('" + endTime + "' as timestamp)");
        } catch (ParseException e) {
            firstSql.append(" and  cast(event_time as date)<=cast('" + behaviorDate.get(1) + "' as date)");
        }
    }

    /**
     * 通过时间yyyy-MM-dd 得到 yyyyMM
     *
     * @param value 时间格式
     * @return yyyyMM
     */
    private String getMonth(String value) {
        if (StringUtils.isNotBlank(value)) {
            String replaceDate = value.replace("-", "");
            return replaceDate.substring(0, 6);
        }
        return null;
    }

    /**
     * 得到第二层sql
     *
     * @param attribute 第二层对象
     * @param param     参数
     * @return sql
     */
    private String getSecondBehaviorConditionSql(BehaviorAttribute attribute, Map<Integer, Object> param) {
        StringBuilder secondSql = new StringBuilder();
        List<SecondBehaviorCondition> secondBehaviorConditionList = attribute.getSecondBehaviorCondition();
        Integer secondLogicalVal = attribute.getSecondLogicalVal();
        for (int i = 0; i < secondBehaviorConditionList.size(); i++) {
            //获得第三层 人群行为 sql
            secondSql.append("(");
            secondSql.append(getThirdBehaviorConditionSql(secondBehaviorConditionList.get(i), param));
            secondSql.append(")");
            if ((i + 1) < secondBehaviorConditionList.size()) {
                //且 取交集
                if (secondLogicalVal == 0) {
                    secondSql.append(" and ");
                } else {
                    secondSql.append(" or ");

                }
            }
        }
        return secondSql.toString();
    }

    /**
     * 人群行为 sql 第三层拼接
     *
     * @param secondBehaviorCondition 第二层对象
     * @param param                   参数
     * @return sql
     */
    private String getThirdBehaviorConditionSql(SecondBehaviorCondition secondBehaviorCondition, Map<Integer, Object> param) {
        StringBuilder thirdSql = new StringBuilder();
        List<ThirdBehaviorCondition> thirdBehaviorConditionList = secondBehaviorCondition.getThirdBehaviorCondition();
        Integer thirdLogicalVal = secondBehaviorCondition.getThirdLogicalVal();
        for (int i = 0; i < thirdBehaviorConditionList.size(); i++) {
            ThirdBehaviorCondition thirdBehaviorCondition = thirdBehaviorConditionList.get(i);
            thirdSql.append(" ( ");
            thirdSql.append(getBehaviorConditionSql(thirdBehaviorCondition, param));
            thirdSql.append(" ) ");
            if ((i + 1) < thirdBehaviorConditionList.size()) {
                //且 取交集
                if (thirdLogicalVal == 0) {
                    thirdSql.append(" and ");
                } else {
                    thirdSql.append(" or ");

                }
            }
        }
        return thirdSql.toString();
    }

    /**
     * 拼接第三层人群行为sql
     *
     * @param thirdBehaviorCondition 第三层人群行为对象
     * @param param                  参数
     * @return sql
     */
    @SuppressWarnings("all")
    private String getBehaviorConditionSql(ThirdBehaviorCondition thirdBehaviorCondition, Map<Integer, Object> param) {
        //查询字段
        String valueParam = thirdBehaviorCondition.getBehaviorAttribute();
        String belongStr = "belong";
        String notBelongStr = "notBelong";
        //数据来源
        String dataSource = thirdBehaviorCondition.getDataSource();
        StringBuilder behaviorConditionSql = new StringBuilder();
        //值    valueType为8是list类型
        Object attributeValue = thirdBehaviorCondition.getBehaviorAttributeValue();
        //值类型
        Integer valueType = thirdBehaviorCondition.getValueType();
        //运算符
        String behaviorAttributeSymbol = thirdBehaviorCondition.getBehaviorAttributeSymbol();
        //修改后的运算符
        String op = getOperation(behaviorAttributeSymbol);
        //组信息 属于不属于
        if (belongStr.equals(behaviorAttributeSymbol) || notBelongStr.equals(behaviorAttributeSymbol)) {
            String belongOp = belongStr.equals(behaviorAttributeSymbol) ? "EXISTS" : " not EXISTS";
//            List split = (List) attributeValue;
//            StringBuilder groupIdS = new StringBuilder();
//            for (int i = 0; i < split.size(); i++) {
//                groupIdS.append("'" + split.get(i) + "'");
//                if (i < (split.size() - 1)) {
//                    groupIdS.append(",");
//                }
//            }
            behaviorConditionSql.append(" " + belongOp + "   ( select 1 from dimdatagroupdetail dt where  dt.project='" + project + "' ");
            behaviorConditionSql.append(" and   dt.dimkey= '" + dataSource + "' and group_id ='" + attributeValue + "' ");
//            behaviorConditionSql.append(" and   dt.dimkey= '" + dataSource + "' and group_id in (" + groupIdS + ") ");
            behaviorConditionSql.append(" and  c." + valueParam + "=dt.key ");
            behaviorConditionSql.append(" )");
        } else if (GroupCalculationValueTypeEnum.DATE.getCode().equals(valueType)) {
            //日期格式
            behaviorConditionSql.append("   date_diff('day',cast( " + valueParam + " as date),cast('" + attributeValue + "' as date))" + op + "0");
//            param.put(param.size() + 1, attributeValue);
        } else if (GroupCalculationValueTypeEnum.DATETIME.getCode().equals(valueType)) {
            if (StringUtils.isNotBlank(op)) {
                behaviorConditionSql.append("  cast(" + valueParam + "  as timestamp) " + op + "cast('" + attributeValue + "' as timestamp)");
//                param.put(param.size() + 1, attributeValue);
            } else {
                //特殊时间判断
                behaviorConditionSql.append(getDateTimeSql(behaviorAttributeSymbol, attributeValue, valueParam, ""));
            }
        } else if (GroupCalculationValueTypeEnum.INT.getCode().equals(valueType)) {
            String betweenStr = "between";
            //整形
            //介于
            if (betweenStr.equals(behaviorAttributeSymbol)) {
                String[] split = attributeValue.toString().split(",");
                String num1 = split[0];
                String num2 = split[1];
                //如果前面的值 比后面的值 大  反转
                if (Integer.valueOf(num1) > Integer.valueOf(num2)) {
                    String num3 = num1;
                    num1 = num2;
                    num2 = num3;
                }
                //判断 大小
                behaviorConditionSql.append("  cast( " + valueParam + "  as int ) >= cast(" + num1 + " as int)");
                behaviorConditionSql.append(" and cast( " + valueParam + "  as int ) <= cast(" + num2 + " as int) ");
            } else {
                //整形
                behaviorConditionSql.append("   cast( " + valueParam + "  as int ) " + op + "  cast(" + attributeValue + " as int)");
                param.put(param.size() + 1, attributeValue);
            }


        } else if (GroupCalculationValueTypeEnum.DECIMAL.getCode().equals(valueType)) {
            String betweenStr = "between";
            //货币 浮点
            //介于
            if (betweenStr.equals(behaviorAttributeSymbol)) {
                String[] split = attributeValue.toString().split(",");
                String num1 = split[0];
                String num2 = split[1];
                //如果前面的值 比后面的值 大  反转
                if (Double.valueOf(num1) > Double.valueOf(num2)) {
                    String num3 = num1;
                    num1 = num2;
                    num2 = num3;
                }
                //判断 大小
                behaviorConditionSql.append("  cast( " + valueParam + "  as double ) >= cast(" + num1 + " as double)");
                behaviorConditionSql.append(" and cast( " + valueParam + "  as double ) <= cast(" + num2 + " as double) ");
            } else {
                //浮点 货币
                behaviorConditionSql.append("   cast(" + valueParam + " as double ) " + op + "  cast(" + attributeValue + " as double)");
                param.put(param.size() + 1, attributeValue);
            }
        } else if (GroupCalculationValueTypeEnum.DIMENSION_LIST.getCode().equals(valueType)
                || GroupCalculationValueTypeEnum.DIMENSION.getCode().equals(valueType)
        ) {
            if ("null".equals(behaviorAttributeSymbol)) {
                behaviorConditionSql.append(valueParam + "    is null");
            } else if ("notnull".equals(behaviorAttributeSymbol)) {
                behaviorConditionSql.append(valueParam + "    is not null");
            } else {
                //查询类型
                //查询类型 字段类型 并且为多个 用in not in的方式
                StringBuilder strInValue = new StringBuilder();
                List split = (List) attributeValue;
                for (int i = 0; i < split.size(); i++) {
                    strInValue.append("'" + split.get(i) + "'");
                    if (i < (split.size() - 1)) {
                        strInValue.append(",");
                    }
                }
                if (" = ".equals(op)) {
                    behaviorConditionSql.append(valueParam + "  in ( " + strInValue + " )");
                } else if (" != ".equals(op)) {
                    behaviorConditionSql.append(valueParam + " not in ( " + strInValue + " )");
                }
            }
        } else {
            //如果为空
            if ("null".equals(behaviorAttributeSymbol)) {
                behaviorConditionSql.append(valueParam + "    is null");
            } else if ("notnull".equals(behaviorAttributeSymbol)) {
                behaviorConditionSql.append(valueParam + "    is not null");
            } else {
                String tmp;
                if ("contains".equals(behaviorAttributeSymbol) || "notcontains".equals(behaviorAttributeSymbol)) {
                    //like '% value %'  not like
                    param.put(param.size() + 1, "%" + attributeValue + "%");
                    tmp = "'%" + attributeValue + "%'";
                } else if ("startwith".equals(behaviorAttributeSymbol)) {
                    //like 'value %'
                    param.put(param.size() + 1, attributeValue + "%");
                    tmp = "'" + attributeValue + "%'";
                } else if ("endwith".equals(behaviorAttributeSymbol)) {
                    //like '% value '
                    param.put(param.size() + 1, "%" + attributeValue);
                    tmp = "'%" + attributeValue + "'";
                } else {
                    param.put(param.size() + 1, attributeValue);
                    tmp = "'" + attributeValue + "'";
                }
                behaviorConditionSql.append(" cast(" + valueParam + " as string) " + op + "cast( " + tmp + " as string)");
            }
        }
        return behaviorConditionSql.toString();
    }

    /**
     * 得到简单运算sql 字段
     *
     * @param limitName 需要运行的
     * @return 对应的sql字段
     */
    private String getlimitName(List<String> limitName) {
        String eventOp = "";
        //如果是  进行count 次数运算 只有list 里只有一个属性
        String limitParam = limitName.get(0);
        if (limitName.size() == 1) {
            eventOp = "count(distinct  event_id)";
        } else {
            String op = limitName.get(1);
            switch (op) {
                case "num":
                    eventOp = "sum(coalesce(" + limitParam + ",0))";
                    break;
                case "beverage":
                    eventOp = "avg(coalesce(" + limitParam + ",0))";
                    break;
                case "max":
                    eventOp = "max(coalesce(" + limitParam + ",0))";
                    break;
                case "min":
                    eventOp = "min(coalesce(" + limitParam + ",0))";
                    break;
                default:
                    break;
            }
        }

        return eventOp;
    }

    public static <T> List<T> getObject(Class<T> classname, ResultSet resultSet) {
        //resultSet为空，直接丢一个空类型
        if (resultSet == null || classname == null) {
            return null;
        }
        Object o = null;
        ArrayList list = new ArrayList();
        try {
            while (resultSet.next()) {
                Constructor[] declaredConstructors = classname.getDeclaredConstructors();
                Constructor constructor = declaredConstructors[0];
                //创建每一个实例对象
                //如果是构造方法时，直接创建对象
                if (constructor.getParameterCount() == 0) {
                    constructor.setAccessible(true);
                    o = constructor.newInstance();
                } else {
                    Object[] objects = new Object[constructor.getParameterCount()];
                    Class[] parameterTypes = constructor.getParameterTypes();
                    for (int i = 0; i < objects.length; i++) {
                        //获得每一个属性的名字
                        String typeName = parameterTypes[i].getTypeName();
                        if ("String".equals(typeName)) {
                            objects[i] = "";
                        }
                        if ("int".equals(typeName)) {
                            objects[i] = 0;
                        }
                        //......
                    }
                    o = constructor.newInstance(objects);
                }
                //获得所有属性
                Field[] fields = classname.getDeclaredFields();
                int len = fields.length;
                for (int i = 0; i < len; i++) {
                    fields[i].setAccessible(true);
                    //获得每个属性的名字
                    String tempName = fields[i].getName();
                    //通过tempName获得rs当前列的值
                    Object value = resultSet.getObject(tempName);
                    //设置这个对象的这个属性的值
                    fields[i].set(o, value);
                }
                //每个对象，加入到集合中
                list.add(o);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (InstantiationException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
        return list;
    }
}
